using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Diagnostics;
using System.Collections;
using System.Globalization;
using System.ComponentModel;
using System.Collections.Generic;
using System.Runtime.Serialization.Formatters.Binary;

namespace ChunkTransporter
{

	/// <summary>
	/// helper methods for data tables
	/// </summary>
	public class DataTableHelper
	{

		#region DataTable - columns

		/// <summary>
		/// return true if a given data column is calculated, or was calculated.
		/// </summary>
		/// <param name="dc"></param>
		/// <returns></returns>
		#region buildColumns
		static public void BuildColumns(DataTable source, string fields, ref DataTable dest)
		{
			if (null == fields || 0 == fields.Length || fields == "*")
			{
				foreach (System.Data.DataColumn col in source.Columns)
					if (!col.ColumnName.EndsWith("___ID"))
						dest.Columns.Add(col.ColumnName, col.DataType);
			}
			else
			{
				foreach (string col in fields.Split(new Char[] { ',' }))
				{
					if (null == source.Columns[col.Trim()] || (col.EndsWith("___ID")))
						continue;
					DataColumn dc = dest.Columns.Add(col.Trim(), source.Columns[col.Trim()].DataType);

				}
			}

			// clone extended properties of each col
			foreach (DataColumn dc in dest.Columns)
				foreach (DictionaryEntry de in source.Columns[dc.ColumnName].ExtendedProperties)
					dc.ExtendedProperties[de.Key] = de.Value;
			if (source.PrimaryKey.Length > 0)
				SetPrimaryKey(ref dest, source.PrimaryKey);
		}
		static public void BuildCols(ref DataTable dest, string[] columnNames, string[] columnTypes)
		{
			Debug.Assert(dest.Columns.Count == 0);

			for (int j = 0; j < columnNames.Length; j++)
				dest.Columns.Add(columnNames[j], System.Type.GetType(columnTypes[j]));
		}
		#endregion
		#endregion

		#region Select

		public static DataTable Select(DataTable source, string fields, IFilter filter)
		{
			if (filter != null)
				source = filter.doFilter(source);

			return Select(source, fields);
		}
		public static DataTable Select(DataTable source, string fields)
		{
			return Select(source, fields, null as string);
		}

		public static void CopyExtendedProperties(DataTable source, ref DataTable dest)
		{
			foreach (DictionaryEntry de in source.ExtendedProperties)
				dest.ExtendedProperties[de.Key] = de.Value;
		}

		/// <summary>
		/// DataTable select()
		/// </summary>
		/// <param name="source"></param>
		/// <param name="fields"></param>
		/// <param name="filter"></param>
		/// <returns></returns>
		public static DataTable Select(DataTable source, string fields, string filter)
		{
			if (source == null)
				return null;
			DataTable dest = new DataTable(source.TableName);
			CopyExtendedProperties(source, ref dest);

			dest.CaseSensitive = source.CaseSensitive;
			BuildColumns(source, fields, ref dest);

			DataColumn[] pk = dest.PrimaryKey;
			//dest.PrimaryKey = null;
			dest.BeginLoadData();

			DataRow[] rows = source.Select(filter);

			if (null == fields || 0 == fields.Length || fields == "*")
			{
				if (source.Columns.Count == dest.Columns.Count)
					foreach (DataRow row in rows)
						dest.Rows.Add(row.ItemArray); //<-- faster
				else
					foreach (DataRow row in rows)
						dest.ImportRow(row); //<-- slow
			}
			else
			{
				foreach (DataRow row in rows)
				{
					DataRow newRow = dest.NewRow();
					foreach (string col in fields.Split(new Char[] { ',' }))
					{
						if (null == row[col.Trim()] || col.EndsWith("___ID"))
							continue;
						newRow[col.Trim()] = row[col.Trim()];
					}
					dest.Rows.Add(newRow);
				}
			}
			try
			{
				dest.EndLoadData();
			}
			catch (System.Data.ConstraintException)
			{
				FindNonUniques(dest, dest.PrimaryKey);
			}
			//dest.PrimaryKey = pk;

			DataColumnCollection colCopy = null;
			try
			{
				colCopy = dest.Columns;
			}
			catch (System.ArgumentException e)
			{
				StringBuilder sb = new StringBuilder();

				sb.AppendFormat(e.Message);
				sb.AppendFormat("{0}.Columns:", dest.TableName);
				foreach (DataColumn dc in dest.Columns)
					sb.AppendFormat("{0} {1}\n ", dc.ColumnName, dc.DataType);
				Helper.Alert(sb.ToString());
				throw e;
			}

			foreach (DataColumn col in colCopy)
				if (col.ColumnName.EndsWith("___ID"))
					dest.Columns.Remove(col);

			return dest;
		}
		#endregion

		public static string SerializeValues(object[] vals)
		{
			StringBuilder sb = new StringBuilder(1000);
			foreach (object o in vals)
				sb.Append(o);
			return sb.ToString();
		}


		#region findMultiple
		
		/// <summary>
		/// find rows from src in dst, per primaryKey.
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="forienKeys"></param>
		/// <returns></returns>
		public static DataRow[] FindNonUniques(DataTable dt, DataColumn[] forienKeys)
		{
			DataTable tmp = dt.Clone();
			DataColumn[] keys = new DataColumn[forienKeys.Length];
			for (int i = 0; i < forienKeys.Length; i++)
				keys[i] = tmp.Columns[forienKeys[i].ColumnName];

			tmp.PrimaryKey = keys;
			List<DataRow> arDoubles = new List<DataRow>();
			if (forienKeys != null && forienKeys.Length > 0)
			{
				foreach (DataRow dr in dt.Rows)
				{
					object[] values = new object[forienKeys.Length];//DataTableHelper.RowValues(dr);
					for (int i = 0; i < forienKeys.Length; i++)
						values[i] = dr[forienKeys[i].ColumnName];

					if (tmp.Rows.Contains(values))
						arDoubles.Add(dr);
					else
						tmp.Rows.Add(dr.ItemArray);
				}
			}
			return arDoubles.ToArray();
		}
		#endregion
		#region SetPrimaryKey

		static public string GetPrimaryKey(DataTable source)
		{
			string sPrimary = string.Empty;
			if (source != null)
				foreach (DataColumn dc in source.PrimaryKey)
					sPrimary += "," + dc.ColumnName;
			return sPrimary;
		}
		static public bool SetPrimaryKey(ref DataTable target, DataTable source)
		{
			Debug.Assert(source.PrimaryKey.Length > 0);
			if (target.PrimaryKey.Length == 0)
			{
				string sPrimary = GetPrimaryKey(source);

				return SetPrimaryKey(ref target, sPrimary);
			}
			else
				return false;
		}

		static public bool SetPrimaryKey(ref DataTable table, DataColumn[] primaryKey)
		{
			Debug.Assert(primaryKey.Length > 0, table.TableName + ".SetPrimaryLey() got null primary key");
			string sPrimary = "";
			foreach (DataColumn dc in primaryKey)
			{
				if (!table.Columns.Contains(dc.ColumnName))
					return false;
				sPrimary += "," + dc.ColumnName;
			}

			return SetPrimaryKey(ref table, sPrimary);
		}
		/// <summary>
		/// This method sets a set of primary keys to a datatable, overriding any existing values.
		/// </summary>
		/// <param name="table">target DataTable to set primary keys for</param>
		/// <param name="primaryKeys">comma separated list e.g. "clientId,AttributeId"</param>
		static public bool SetPrimaryKey(ref DataTable table, string primaryKeys)
		{
			Debug.Assert(primaryKeys != null && primaryKeys.Length > 0, table.TableName + ".SetPrimaryKey() got null as primaryKeys.");
			if (table.PrimaryKey.Length == 0 && primaryKeys != null && primaryKeys.Length > 0)
			{
				DataColumn[] keys = null;
				string[] arPrimaryKeys = primaryKeys.Trim(new char[] { ',' }).Split(new char[] { ',' });
				keys = new DataColumn[arPrimaryKeys.Length];
				short i = 0;
				foreach (string key in arPrimaryKeys)
				{
					string primaryKey = key.Trim();
					if (key.Length < 1)
						continue;
					if (!table.Columns.Contains(primaryKey))
						return false;//throw new ITG.ApplicationServer.Exceptions.InvalidPrimaryKeyException(table.TableName, key);

					keys[i++] = table.Columns[primaryKey];
				}
				try
				{
					table.PrimaryKey = keys;
				}
				catch (System.ArgumentException e)
				{
					DataRow[] dups = FindNonUniques(table, keys);
					if (dups.Length > 0)
					{
						Helper.TRACE("{0}.primaryKey = {1} failed!", table.TableName, primaryKeys);
						System.Text.StringBuilder sb = new System.Text.StringBuilder();
						foreach (DataRow dr in dups)
						{
							sb.AppendFormat(" {0} = ", primaryKeys);

							foreach (DataColumn dc in keys)
								sb.AppendFormat(" {0} ,", dr[dc.ColumnName]);
							sb.AppendFormat("\r\n");
						}

						Helper.TRACE("dupes: {0}", sb.ToString());
					}
					throw e;
				}
			}
			return true;
		}
		#endregion
		#region trim
		/// <summary>
		/// Trim white spaces in string columns
		/// </summary>
		/// <param name="dt"></param>
		public static void Trim(ref DataTable dt)
		{
			if (dt == null)
				return;
			dt.BeginLoadData();
			try
			{
				foreach (System.Data.DataColumn col in dt.Columns)
				{
					if (typeof(System.String) == col.DataType)
						foreach (DataRow row in dt.Rows)
							row[col.Ordinal] = System.Convert.ToString(row[col.Ordinal]).TrimEnd();
				}
			}
			finally
			{
				dt.EndLoadData();
			}
		}
		#endregion
		#region copy
		#endregion
		#region DataTable conversions
		#region DataTable - Array
		/// <summary>
		/// convert a data table to a b-dimenssion array of objects.
		/// </summary>
		/// <param name="table"></param>
		/// <returns></returns>
		public static object[,] DataTable2Array(DataTable table)
		{//TBD: add column names
			if (table == null)
				return null;
			object[,] retval = new object[table.Rows.Count + 1, table.Columns.Count];

			for (int j = 0; j < table.Columns.Count; j++)
				retval[0, j] = table.Columns[j].ColumnName;

			for (int i = 0; i < table.Rows.Count; i++)
				for (int j = 0; j < table.Columns.Count; j++)
					retval[i + 1, j] = table.Rows[i][j];

			return retval;
		}
		/// <summary>
		/// convert object array to data table.
		/// </summary>
		/// <param name="arr"></param>
		/// <returns></returns>
		public static DataTable Array2DataTable(object[,] arr)
		{//TBD: column names
			if (arr == null)
				return null;

			DataTable retval = new DataTable();
			retval.CaseSensitive = true;
			for (int j = 0; j < arr.GetLength(1); j++)
				retval.Columns.Add(arr[0, j].ToString());

			object[] items = new object[arr.GetLength(1)];
			for (int i = 0; i < arr.GetLength(0); i++)
			{
				for (int j = 0; j < arr.GetLength(1); j++)
					items[j] = arr[i + 1, j];
				retval.Rows.Add(items);
			}

			return retval;
		}

		#endregion

		#region DataTable - string
		public const string DELIM = "\x1f";
		public const string NULL = "\x0";
		public static string[] DataTable2String(DataTable table, bool isTrim)
		{
			if (table == null)
				return null;
			StringBuilder[] retval = new StringBuilder[table.Rows.Count + 2];
			string[] retval2 = new String[table.Rows.Count + 2];
			for (int i = 0; i < retval.Length; i++)
				retval[i] = new StringBuilder(1000);

			for (int j = 0; j < table.Columns.Count; j++)
			{
				retval[0].Append(table.Columns[j].ColumnName + ",");
				retval[1].Append(table.Columns[j].DataType + ",");
			}

			retval2[0] = retval[0].ToString().TrimEnd(",".ToCharArray());
			retval2[1] = retval[1].ToString().TrimEnd(",".ToCharArray());

			System.Type TYPEOF_STRING = typeof(string);
			System.Type TYPEOF_DATETIME = typeof(DateTime);

			for (int i = 0; i < table.Rows.Count; i++)
			{
				System.Data.DataRow dr = table.Rows[i];
				for (int j = 0; j < table.Columns.Count; j++)
				{
					if (System.Convert.DBNull == dr[j])
						retval[i + 2].Append(NULL);
					else
					{
						Type dataType = table.Columns[j].DataType;
						if (dataType == TYPEOF_DATETIME)
							retval[i + 2].Append(Helper.Time2DbString((DateTime)dr[j], true));
						else
						{
							if (isTrim && dataType == TYPEOF_STRING)
								retval[i + 2].Append(dr[j].ToString().TrimEnd());
							else
								retval[i + 2].Append(dr[j].ToString());
						}
					}
					retval[i + 2].Append(DELIM);
				}

				retval2[i + 2] = retval[i + 2].ToString().TrimEnd(DELIM.ToCharArray());
			}
			return retval2;
		}
		public static string[] DataRows2String(DataRow[] rows, bool isTrim)
		{
			if (rows == null || rows.Length == 0)
				return null;
			DataTable parentTable = rows[0].Table;
			if (parentTable == null)
				return null;
			StringBuilder[] retval = new StringBuilder[rows.Length + 2];
			string[] retval2 = new String[rows.Length + 2];
			for (int i = 0; i < retval.Length; i++)
				retval[i] = new StringBuilder(1000);

			for (int j = 0; j < parentTable.Columns.Count; j++)
			{
				retval[0].Append(parentTable.Columns[j].ColumnName + ",");
				retval[1].Append(parentTable.Columns[j].DataType + ",");
			}

			retval2[0] = retval[0].ToString().TrimEnd(",".ToCharArray());
			retval2[1] = retval[1].ToString().TrimEnd(",".ToCharArray());

			System.Type TYPEOF_STRING = typeof(string);
			System.Type TYPEOF_DATETIME = typeof(DateTime);

			for (int i = 0; i < rows.Length; i++)
			{
				System.Data.DataRow dr = rows[i];
				for (int j = 0; j < parentTable.Columns.Count; j++)
				{
					if (System.Convert.DBNull == dr[j])
						retval[i + 2].Append(NULL);
					else
					{
						Type dataType = parentTable.Columns[j].DataType;
						if (dataType == TYPEOF_DATETIME)
							retval[i + 2].Append(Helper.Time2DbString((DateTime)dr[j], true));
						else
						{
							if (isTrim && dataType == TYPEOF_STRING)
								retval[i + 2].Append(dr[j].ToString().TrimEnd());
							else
								retval[i + 2].Append(dr[j].ToString());
						}
					}
					retval[i + 2].Append(DELIM);
				}

				retval2[i + 2] = retval[i + 2].ToString().TrimEnd(DELIM.ToCharArray());
			}
			return retval2;
		}

		public static DataTable String2DataTable(string[] str)
		{
			return String2DataTable("String2DataTable", str);
		}

		public static DataTable String2DataTable(string tableName, string[] str)
		{
			if (str == null)
				return null;
			DataTable retval = new DataTable(tableName);
			retval.CaseSensitive = true;
			retval.RemotingFormat = SerializationFormat.Binary;
			string[] columnNames = str[0].Split(",".ToCharArray());
			string[] columnTypes = str[1].Split(",".ToCharArray());
			BuildCols(ref retval, columnNames, columnTypes);

			retval.BeginLoadData();
			for (int i = 2; i < str.Length; i++)
			{
				string[] items = str[i].Split(DELIM.ToCharArray());
				try
				{
					// if "\x0" -> make null
					for (int j = 0; j < items.Length; j++)
						if (NULL == items[j])
							items[j] = null;

					DataRow row = retval.NewRow();
					row.ItemArray = items;
					retval.Rows.Add(row);

					//retval.Rows.Add(items);
				}
				catch (Exception e)
				{
					Debug.WriteLine(str[0]);
					Debug.WriteLine(str[1]);
					Debug.WriteLine(str[i]);
					for (int j = 0; j < items.Length; j++)
						Debug.WriteLine(String.Format("{0}-{1}:'{2}'", columnNames[j], columnTypes[j], items[j]));

					Debug.WriteLine(e);

				}
			}

			retval.EndLoadData();
			return retval;
		}

		#endregion
	
		#endregion


		public static Stream SerializeTable(DataTable dt)
		{
			BinaryFormatter bformatter = new BinaryFormatter();
			MemoryStream stream = new MemoryStream();
			bformatter.Serialize(stream, dt);
			return stream;
		}
		public static DataTable DeserializeTable(Stream s)
		{
			return new BinaryFormatter().Deserialize(s) as DataTable;
		}

		/// <summary>
		/// interpolates table size
		/// </summary>
		/// <param name="t"></param>
		/// <returns></returns>
		public static int InterpolateTableSize(DataTable t)
		{
			try
			{
				const int SAMPLE_SIZE = 10;

				BinaryFormatter bformatter = new BinaryFormatter();
				using (MemoryStream stream = new MemoryStream())
				{
					bformatter.Serialize(stream, t.Clone());

					for (int i = 0; i < SAMPLE_SIZE; i++)
						foreach (object o in t.Rows[new Random().Next(t.Rows.Count - 1)].ItemArray)
							bformatter.Serialize(stream, o);

					return System.Convert.ToInt32(stream.Length * t.Rows.Count / SAMPLE_SIZE);
				}
			}
			catch (Exception e)
			{
				Helper.LogException("SizeofTable()", e);
				return 0;
			}
		}

	}

}
